package com.xiaodu.jdbc.controller;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.xiaodu.jdbc.entity.UserEntity;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamResource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileOutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static java.util.stream.Collectors.toList;

/**
 * @author: dutianjie
 * @date: 2021/4/9
 * @version: v1.0.0
 * @description: 描述
 **/
@RestController
@RequestMapping("user")
public class UserController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * 使用jdbcTemplate.update 进行insert操作
     * @param userEntity
     * @return
     * @throws Exception
     */
    @PostMapping({"", "add"})
    public String addUser(UserEntity userEntity) throws Exception {
        String id = UUID.randomUUID().toString().replaceAll("-", "");
        Object[] params = new Object[]{id,
                userEntity.getuName(),
                userEntity.getPassword(),
                userEntity.getMoney(),
                new Date()};
        jdbcTemplate.update("insert into t_user(u_id,u_name,password,money,create_time) values(?,?,?,?,?)", params);
        return id;
    }

    /**
     * 使用jdbcTemplate.batchUpdate 进行批量添加操作
     * @param userEntityList
     * @return
     */
    @PostMapping({"/batch"})
    public List<String> batchAddUser(@RequestBody List<UserEntity> userEntityList) {
        List<UserEntity> collect = userEntityList.stream().map(u -> {
            u.setuId(UUID.randomUUID().toString().replaceAll("-", ""));
            u.setCreateTime(new Date());
            return u;
        }).collect(toList());

        jdbcTemplate.batchUpdate("insert into t_user(u_id,u_name,`password`,money,create_time) values(?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, collect.get(i).getuId());
                ps.setString(2, collect.get(i).getuName());
                ps.setString(3, collect.get(i).getPassword());
                ps.setLong(4, collect.get(i).getMoney());
                ps.setTimestamp(5, new Timestamp(collect.get(i).getCreateTime().getTime()));
            }

            @Override
            public int getBatchSize() {
                return collect.size();
            }
        });
        return collect.stream().map(UserEntity::getuId).collect(toList());
    }


    /**
     * 使用jdbcTemplate.QueryForObject 进行selectOne操作
     * @param id
     * @return
     * @throws Exception
     */
    @GetMapping({"{id}"})
    public UserEntity getUser(@PathVariable String id) throws Exception {
        return jdbcTemplate.queryForObject("select * from t_user where u_id = ?", new Object[]{id}, new BeanPropertyRowMapper<>(UserEntity.class));
    }

    /**
     *jdbcTemplate.queryForMap 进行selectOne操作
     * @param id
     * @return
     * @throws Exception
     */
    @GetMapping({"v2/{id}"})
    public Map<String, Object> getUser2(@PathVariable String id) throws Exception {
        return jdbcTemplate.queryForMap("select * from t_user where u_id = ?", new Object[]{id}, new BeanPropertyRowMapper<>(UserEntity.class));
    }

    /**
     * 使用jdbcTemplate 进行count 操作
     * @return
     * @throws Exception
     */
    @GetMapping("count")
    public Long getUserCount() throws Exception {
        return jdbcTemplate.queryForObject("select count(u_id) from t_user", Long.class);
    }

    /**
     * 使用jdbcTemplate.query 进行批量查询操作
     * @param ids
     * @return
     * @throws Exception
     */
    @GetMapping("batch")
    public List<UserEntity> getBatchUser(@RequestParam(name = "ids") String[] ids) throws Exception {
        String params = Stream.generate(() -> "?").limit(ids.length).collect(Collectors.joining(",", "", ""));
        return jdbcTemplate.query("select * from t_user where u_id in (" + params + ")", ids, new BeanPropertyRowMapper<>(UserEntity.class));
    }

    /**
     * 使用 jdbcTemplate.queryForList 进行批量查询操作
     * @param ids
     * @return
     * @throws Exception
     */
    @GetMapping("batch2")
    public List<Map<String, Object>> getBatchUsers(@RequestParam(name = "ids") String[] ids) throws Exception {
        String params = Stream.generate(() -> "?").limit(ids.length).collect(Collectors.joining(",", "", ""));
        return jdbcTemplate.queryForList("select * from t_user where u_id in (" + params + ")", ids);
    }


    /**
     *  使用namedParameterJdbcTemplate.update 进行更新操作
     * @param userEntity
     * @throws Exception
     */
    @PutMapping()
    public void update(@RequestBody UserEntity userEntity) throws Exception {
        Objects.requireNonNull(userEntity.getuId());
//        jdbcTemplate.update("update t_user set u_name = ?, password = ? where u_id = ?", new Object[]{userEntity.getuName(), userEntity.getPassword(), userEntity.getuId()});
        // 使用namedParameterJdbcTemplate 进行操作
        namedParameterJdbcTemplate.update("update t_user set u_name = :uName, password = :password where u_id = :uId", new ObjectMapper().convertValue(userEntity, Map.class));
    }

    /**
     * 使用jdbcTemplate.update 进行 delete 操作
     * @param id
     * @throws Exception
     */
    @DeleteMapping("{id}")
    public void del(@PathVariable String id) throws Exception {
        Objects.requireNonNull(id);
        jdbcTemplate.update("delete from t_user where u_id = ?", new Object[]{id});
    }

    /**
     * 使用jdbcTemplate.execute 进行批量删除
     * @param ids
     * @throws Exception
     */
    @DeleteMapping("batch")
    public void del(@RequestBody List<String> ids) throws Exception {
        String collect = Stream.generate(() -> "?").limit(ids.size()).collect(Collectors.joining(",", "", ""));
        jdbcTemplate.execute("delete from t_user where u_id in (" + collect + ")", new PreparedStatementCallback<Integer>() {
            @Override
            public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                for (int i = 0; i < ids.size(); i++) {
                    ps.setString(i + 1, ids.get(i));
                }
                return ps.executeUpdate();
            }
        });
    }

    // 上传文件，配合restTemplate 使用
    @PostMapping("file")
    public void upload(@RequestPart(name = "file") MultipartFile file) throws Exception {
        System.out.println(file.getOriginalFilename());
    }

    // 上传文件，配合restTemplate 使用
    @PostMapping("file2")
    public void upload(HttpServletRequest request) throws Exception {
        FileOutputStream fileOutputStream = new FileOutputStream("F:\\app\\aaa.jpg");
        IOUtils.copy(request.getInputStream(), fileOutputStream);
        IOUtils.closeQuietly(fileOutputStream);
    }
}
